import pandas as pd 
import numpy as np
from scipy.stats import pearsonr

import matplotlib.pyplot as pltpy
data = pd.read_excel('data/avg_month.xlsx','Sheet1')
# print(data)

# 处理数据
def remove(data):
    date = data.values.tolist()[0][0].split()[0]
    result=[]
    result2={}
    result2['title']=[]
    i=0
    for data1 in data:
        if(i!=0):
            result2['title'].append(data1)
        i = 1
    
    for value in data.values: 
        
        if(date!=value[0].split()[0]):
            
            result2[date]=np.array(result).T.tolist()
            date = value[0].split()[0]
            # print(date)
            result=[]
        result.append(value[1:])
    # print(result2.keys())
    result2[date]=np.array(result).T.tolist()
    return result2
# print(data.values.tolist())
# remove(data)
    
def concatData(data,date):
    # print('nihao',data['2018'])
    title = data['title']
    data = data[str(date)]
    result ={}
    i = 0
    for t in title:
        result[t] = data[i]
        i = i+1
    return result
    



# data_remove = remove(data)
# print(data_remove)
# concatData(data_remove)
data = pd.read_excel('data/monthly_data.xlsx','Sheet1')
def remove2(data,date='2018'):
    date = int(date) - 2018
    i=0
    result = {}
    for data1 in data.values:
        if(i==2 or i==6 or i==10):
            result[data1[0]]=data1[date*12+1:date*12+13].tolist()
        i = i+1
    # print(result)
    return result
# data1 = remove2(data)
# data = pd.read_excel('data/avg_month.xlsx','Sheet1')
# data2 = concatData(remove(data),'2018')

def getPearson(data_monthly,data_avg_month):
    
    pearsonr_ = []
    tmp = {"USD/CNY":[],"EUR/CNY":[],"HKD/CNY":[],"GBP/CNY":[],"100JPY/CNY":[]}
    i = 0
    while (i<6):
        d1 = remove2(data_monthly,2018+i)["进出口总值当期值(千美元)"]
        d2 = concatData(remove(data_avg_month),i+2018)
        for k,v in d2.items():
            # print(k,v)
            corr, p_value = pearsonr(d1, v)
            tmp[k].append(corr)
        i = i+1 
    return tmp
    # print(data1["进出口总值当期值(千美元)"])
    # print("==========================")
    # print(data2["USD/CNY"])
    # # print(len())
    # # print(len(y))
    
    # corr, p_value = pearsonr(data1["进出口总值当期值(千美元)"], data2["USD/CNY"])

    # # 打印结果
    # print('皮尔逊相关系数:', corr)
    # print('p-value:', p_value)
        
# getPearson()